{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Window LAG\n",
    "\n",
    "## COVID-19 Data\n",
    "Notes on the data: This data was assembled based on work done by [Rodrigo Pombo](https://github.com/pomber/covid19) based on [John Hopkins University](https://systems.jhu.edu/research/public-health/ncov/), based on [World Health Organisation](https://www.who.int/health-topics/coronavirus). The data was assembled 21st April 2020 - there are no plans to keep this data set up to date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "── \u001b[1mAttaching packages\u001b[22m ─────────────────────────────────────── tidyverse 1.3.0 ──\n",
      "\n",
      "\u001b[32m✔\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32m✔\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32m✔\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32m✔\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32m✔\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32m✔\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32m✔\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32m✔\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "── \u001b[1mConflicts\u001b[22m ────────────────────────────────────────── tidyverse_conflicts() ──\n",
      "\u001b[31m✖\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31m✖\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ····\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Window Function\n",
    "The SQL Window functions include LAG, LEAD, RANK and NTILE. These functions operate over a \"window\" of rows - typically these are rows in the table that are in some sense adjacent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "covid <- dbReadTable(con, 'covid')\n",
    "world <- dbReadTable(con, 'world')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introducing the `covid` table\n",
    "\n",
    "The example uses a WHERE clause to show the cases in 'Italy' in March.\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn),\n",
    " confirmed, deaths, recovered\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "**Modify the query to show data from Spain**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 31 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>day</th><th scope=col>confirmed</th><th scope=col>deaths</th><th scope=col>recovered</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int64&gt;</th><th scope=col>&lt;int64&gt;</th><th scope=col>&lt;int64&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Spain</td><td>01</td><td>  84</td><td> 0</td><td> 2</td></tr>\n",
       "\t<tr><td>Spain</td><td>02</td><td> 120</td><td> 0</td><td> 2</td></tr>\n",
       "\t<tr><td>Spain</td><td>03</td><td> 165</td><td> 1</td><td> 2</td></tr>\n",
       "\t<tr><td>Spain</td><td>04</td><td> 222</td><td> 2</td><td> 2</td></tr>\n",
       "\t<tr><td>Spain</td><td>05</td><td> 259</td><td> 3</td><td> 2</td></tr>\n",
       "\t<tr><td>Spain</td><td>06</td><td> 400</td><td> 5</td><td> 2</td></tr>\n",
       "\t<tr><td>Spain</td><td>07</td><td> 500</td><td>10</td><td>30</td></tr>\n",
       "\t<tr><td>Spain</td><td>08</td><td> 673</td><td>17</td><td>30</td></tr>\n",
       "\t<tr><td>Spain</td><td>09</td><td>1073</td><td>28</td><td>32</td></tr>\n",
       "\t<tr><td>Spain</td><td>10</td><td>1695</td><td>35</td><td>32</td></tr>\n",
       "\t<tr><td>⋮</td><td>⋮</td><td>⋮</td><td>⋮</td><td>⋮</td></tr>\n",
       "\t<tr><td>Spain</td><td>22</td><td>28768</td><td>1772</td><td> 2575</td></tr>\n",
       "\t<tr><td>Spain</td><td>23</td><td>35136</td><td>2311</td><td> 2575</td></tr>\n",
       "\t<tr><td>Spain</td><td>24</td><td>39885</td><td>2808</td><td> 3794</td></tr>\n",
       "\t<tr><td>Spain</td><td>25</td><td>49515</td><td>3647</td><td> 5367</td></tr>\n",
       "\t<tr><td>Spain</td><td>26</td><td>57786</td><td>4365</td><td> 7015</td></tr>\n",
       "\t<tr><td>Spain</td><td>27</td><td>65719</td><td>5138</td><td> 9357</td></tr>\n",
       "\t<tr><td>Spain</td><td>28</td><td>73235</td><td>5982</td><td>12285</td></tr>\n",
       "\t<tr><td>Spain</td><td>29</td><td>80110</td><td>6803</td><td>14709</td></tr>\n",
       "\t<tr><td>Spain</td><td>30</td><td>87956</td><td>7716</td><td>16780</td></tr>\n",
       "\t<tr><td>Spain</td><td>31</td><td>95923</td><td>8464</td><td>19259</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 31 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " name & day & confirmed & deaths & recovered\\\\\n",
       " <chr> & <chr> & <int64> & <int64> & <int64>\\\\\n",
       "\\hline\n",
       "\t Spain & 01 &   84 &  0 &  2\\\\\n",
       "\t Spain & 02 &  120 &  0 &  2\\\\\n",
       "\t Spain & 03 &  165 &  1 &  2\\\\\n",
       "\t Spain & 04 &  222 &  2 &  2\\\\\n",
       "\t Spain & 05 &  259 &  3 &  2\\\\\n",
       "\t Spain & 06 &  400 &  5 &  2\\\\\n",
       "\t Spain & 07 &  500 & 10 & 30\\\\\n",
       "\t Spain & 08 &  673 & 17 & 30\\\\\n",
       "\t Spain & 09 & 1073 & 28 & 32\\\\\n",
       "\t Spain & 10 & 1695 & 35 & 32\\\\\n",
       "\t ⋮ & ⋮ & ⋮ & ⋮ & ⋮\\\\\n",
       "\t Spain & 22 & 28768 & 1772 &  2575\\\\\n",
       "\t Spain & 23 & 35136 & 2311 &  2575\\\\\n",
       "\t Spain & 24 & 39885 & 2808 &  3794\\\\\n",
       "\t Spain & 25 & 49515 & 3647 &  5367\\\\\n",
       "\t Spain & 26 & 57786 & 4365 &  7015\\\\\n",
       "\t Spain & 27 & 65719 & 5138 &  9357\\\\\n",
       "\t Spain & 28 & 73235 & 5982 & 12285\\\\\n",
       "\t Spain & 29 & 80110 & 6803 & 14709\\\\\n",
       "\t Spain & 30 & 87956 & 7716 & 16780\\\\\n",
       "\t Spain & 31 & 95923 & 8464 & 19259\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 31 × 5\n",
       "\n",
       "| name &lt;chr&gt; | day &lt;chr&gt; | confirmed &lt;int64&gt; | deaths &lt;int64&gt; | recovered &lt;int64&gt; |\n",
       "|---|---|---|---|---|\n",
       "| Spain | 01 |   84 |  0 |  2 |\n",
       "| Spain | 02 |  120 |  0 |  2 |\n",
       "| Spain | 03 |  165 |  1 |  2 |\n",
       "| Spain | 04 |  222 |  2 |  2 |\n",
       "| Spain | 05 |  259 |  3 |  2 |\n",
       "| Spain | 06 |  400 |  5 |  2 |\n",
       "| Spain | 07 |  500 | 10 | 30 |\n",
       "| Spain | 08 |  673 | 17 | 30 |\n",
       "| Spain | 09 | 1073 | 28 | 32 |\n",
       "| Spain | 10 | 1695 | 35 | 32 |\n",
       "| ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |\n",
       "| Spain | 22 | 28768 | 1772 |  2575 |\n",
       "| Spain | 23 | 35136 | 2311 |  2575 |\n",
       "| Spain | 24 | 39885 | 2808 |  3794 |\n",
       "| Spain | 25 | 49515 | 3647 |  5367 |\n",
       "| Spain | 26 | 57786 | 4365 |  7015 |\n",
       "| Spain | 27 | 65719 | 5138 |  9357 |\n",
       "| Spain | 28 | 73235 | 5982 | 12285 |\n",
       "| Spain | 29 | 80110 | 6803 | 14709 |\n",
       "| Spain | 30 | 87956 | 7716 | 16780 |\n",
       "| Spain | 31 | 95923 | 8464 | 19259 |\n",
       "\n"
      ],
      "text/plain": [
       "   name  day confirmed deaths recovered\n",
       "1  Spain 01    84       0      2       \n",
       "2  Spain 02   120       0      2       \n",
       "3  Spain 03   165       1      2       \n",
       "4  Spain 04   222       2      2       \n",
       "5  Spain 05   259       3      2       \n",
       "6  Spain 06   400       5      2       \n",
       "7  Spain 07   500      10     30       \n",
       "8  Spain 08   673      17     30       \n",
       "9  Spain 09  1073      28     32       \n",
       "10 Spain 10  1695      35     32       \n",
       "⋮  ⋮     ⋮   ⋮         ⋮      ⋮        \n",
       "22 Spain 22  28768     1772    2575    \n",
       "23 Spain 23  35136     2311    2575    \n",
       "24 Spain 24  39885     2808    3794    \n",
       "25 Spain 25  49515     3647    5367    \n",
       "26 Spain 26  57786     4365    7015    \n",
       "27 Spain 27  65719     5138    9357    \n",
       "28 Spain 28  73235     5982   12285    \n",
       "29 Spain 29  80110     6803   14709    \n",
       "30 Spain 30  87956     7716   16780    \n",
       "31 Spain 31  95923     8464   19259    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "covid %>% \n",
    "    filter(name=='Spain' & format(whn, '%m')=='03') %>%\n",
    "    mutate(day=format(whn, '%d')) %>%\n",
    "    select(name, day, confirmed, deaths, recovered) %>%\n",
    "    arrange(day)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Introducing the LAG function\n",
    "\n",
    "The LAG function is used to show data from the preceding row or the table. When lining up rows the data is partitioned by country name and ordered by the data whn. That means that only data from Italy is considered.\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(whn, 1) OVER (PARTITION BY name ORDER BY whn)\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "**Modify the query to show confirmed for the day before.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 31 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>day</th><th scope=col>confirmed</th><th scope=col>lag_cfrm</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int64&gt;</th><th scope=col>&lt;int64&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Italy</td><td>01</td><td> 1694</td><td>  NA</td></tr>\n",
       "\t<tr><td>Italy</td><td>02</td><td> 2036</td><td>1694</td></tr>\n",
       "\t<tr><td>Italy</td><td>03</td><td> 2502</td><td>2036</td></tr>\n",
       "\t<tr><td>Italy</td><td>04</td><td> 3089</td><td>2502</td></tr>\n",
       "\t<tr><td>Italy</td><td>05</td><td> 3858</td><td>3089</td></tr>\n",
       "\t<tr><td>Italy</td><td>06</td><td> 4636</td><td>3858</td></tr>\n",
       "\t<tr><td>Italy</td><td>07</td><td> 5883</td><td>4636</td></tr>\n",
       "\t<tr><td>Italy</td><td>08</td><td> 7375</td><td>5883</td></tr>\n",
       "\t<tr><td>Italy</td><td>09</td><td> 9172</td><td>7375</td></tr>\n",
       "\t<tr><td>Italy</td><td>10</td><td>10149</td><td>9172</td></tr>\n",
       "\t<tr><td>⋮</td><td>⋮</td><td>⋮</td><td>⋮</td></tr>\n",
       "\t<tr><td>Italy</td><td>22</td><td> 59138</td><td> 53578</td></tr>\n",
       "\t<tr><td>Italy</td><td>23</td><td> 63927</td><td> 59138</td></tr>\n",
       "\t<tr><td>Italy</td><td>24</td><td> 69176</td><td> 63927</td></tr>\n",
       "\t<tr><td>Italy</td><td>25</td><td> 74386</td><td> 69176</td></tr>\n",
       "\t<tr><td>Italy</td><td>26</td><td> 80589</td><td> 74386</td></tr>\n",
       "\t<tr><td>Italy</td><td>27</td><td> 86498</td><td> 80589</td></tr>\n",
       "\t<tr><td>Italy</td><td>28</td><td> 92472</td><td> 86498</td></tr>\n",
       "\t<tr><td>Italy</td><td>29</td><td> 97689</td><td> 92472</td></tr>\n",
       "\t<tr><td>Italy</td><td>30</td><td>101739</td><td> 97689</td></tr>\n",
       "\t<tr><td>Italy</td><td>31</td><td>105792</td><td>101739</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 31 × 4\n",
       "\\begin{tabular}{llll}\n",
       " name & day & confirmed & lag\\_cfrm\\\\\n",
       " <chr> & <chr> & <int64> & <int64>\\\\\n",
       "\\hline\n",
       "\t Italy & 01 &  1694 &   NA\\\\\n",
       "\t Italy & 02 &  2036 & 1694\\\\\n",
       "\t Italy & 03 &  2502 & 2036\\\\\n",
       "\t Italy & 04 &  3089 & 2502\\\\\n",
       "\t Italy & 05 &  3858 & 3089\\\\\n",
       "\t Italy & 06 &  4636 & 3858\\\\\n",
       "\t Italy & 07 &  5883 & 4636\\\\\n",
       "\t Italy & 08 &  7375 & 5883\\\\\n",
       "\t Italy & 09 &  9172 & 7375\\\\\n",
       "\t Italy & 10 & 10149 & 9172\\\\\n",
       "\t ⋮ & ⋮ & ⋮ & ⋮\\\\\n",
       "\t Italy & 22 &  59138 &  53578\\\\\n",
       "\t Italy & 23 &  63927 &  59138\\\\\n",
       "\t Italy & 24 &  69176 &  63927\\\\\n",
       "\t Italy & 25 &  74386 &  69176\\\\\n",
       "\t Italy & 26 &  80589 &  74386\\\\\n",
       "\t Italy & 27 &  86498 &  80589\\\\\n",
       "\t Italy & 28 &  92472 &  86498\\\\\n",
       "\t Italy & 29 &  97689 &  92472\\\\\n",
       "\t Italy & 30 & 101739 &  97689\\\\\n",
       "\t Italy & 31 & 105792 & 101739\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 31 × 4\n",
       "\n",
       "| name &lt;chr&gt; | day &lt;chr&gt; | confirmed &lt;int64&gt; | lag_cfrm &lt;int64&gt; |\n",
       "|---|---|---|---|\n",
       "| Italy | 01 |  1694 |   NA |\n",
       "| Italy | 02 |  2036 | 1694 |\n",
       "| Italy | 03 |  2502 | 2036 |\n",
       "| Italy | 04 |  3089 | 2502 |\n",
       "| Italy | 05 |  3858 | 3089 |\n",
       "| Italy | 06 |  4636 | 3858 |\n",
       "| Italy | 07 |  5883 | 4636 |\n",
       "| Italy | 08 |  7375 | 5883 |\n",
       "| Italy | 09 |  9172 | 7375 |\n",
       "| Italy | 10 | 10149 | 9172 |\n",
       "| ⋮ | ⋮ | ⋮ | ⋮ |\n",
       "| Italy | 22 |  59138 |  53578 |\n",
       "| Italy | 23 |  63927 |  59138 |\n",
       "| Italy | 24 |  69176 |  63927 |\n",
       "| Italy | 25 |  74386 |  69176 |\n",
       "| Italy | 26 |  80589 |  74386 |\n",
       "| Italy | 27 |  86498 |  80589 |\n",
       "| Italy | 28 |  92472 |  86498 |\n",
       "| Italy | 29 |  97689 |  92472 |\n",
       "| Italy | 30 | 101739 |  97689 |\n",
       "| Italy | 31 | 105792 | 101739 |\n",
       "\n"
      ],
      "text/plain": [
       "   name  day confirmed lag_cfrm\n",
       "1  Italy 01   1694       NA    \n",
       "2  Italy 02   2036     1694    \n",
       "3  Italy 03   2502     2036    \n",
       "4  Italy 04   3089     2502    \n",
       "5  Italy 05   3858     3089    \n",
       "6  Italy 06   4636     3858    \n",
       "7  Italy 07   5883     4636    \n",
       "8  Italy 08   7375     5883    \n",
       "9  Italy 09   9172     7375    \n",
       "10 Italy 10  10149     9172    \n",
       "⋮  ⋮     ⋮   ⋮         ⋮       \n",
       "22 Italy 22   59138     53578  \n",
       "23 Italy 23   63927     59138  \n",
       "24 Italy 24   69176     63927  \n",
       "25 Italy 25   74386     69176  \n",
       "26 Italy 26   80589     74386  \n",
       "27 Italy 27   86498     80589  \n",
       "28 Italy 28   92472     86498  \n",
       "29 Italy 29   97689     92472  \n",
       "30 Italy 30  101739     97689  \n",
       "31 Italy 31  105792    101739  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "covid %>%\n",
    "    mutate(mo=format(whn, '%m'), \n",
    "           day=format(whn, '%d')) %>%\n",
    "    filter(name=='Italy' & mo =='03') %>%\n",
    "    group_by(name, mo) %>% \n",
    "    mutate(lag_cfrm=lag(\n",
    "        confirmed, default=bit64::NA_integer64_, order_by=whn)) %>%\n",
    "    mutate(day=format(whn, '%d')) %>%\n",
    "    ungroup %>%\n",
    "    select(name, day, confirmed, lag_cfrm)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LAG operation\n",
    "\n",
    "Here is the correct query showing the cases for the day before:\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(confirmed, 1) OVER (partition by name ORDER BY whn) AS lag\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "Notice how the values in the LAG column match the value of the row diagonally above and to the left.\n",
    "\n",
    "name | DAY(whn) | confirmed | dbf\n",
    "------|---|------|----------\n",
    "Italy | 1 | **1694** | null\n",
    "Italy | 2 | 2036 | **1694**\n",
    "Italy | 3 | 2502 | 2036\n",
    "Italy | 4 | 3089 | 2502\n",
    "Italy | 5 | **3858** | 3089\n",
    "Italy | 6 | 4636 | **3858**\n",
    "Italy | 7 | 5883 | 4636\n",
    "Italy | 8 | 7375 | 5883\n",
    "Italy | 9 | 9172 | 7375\n",
    "Italy | 10 | 10149 | 9172\n",
    "... | | |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Number of new cases\n",
    "\n",
    "The number of confirmed case is cumulative - but we can use LAG to recover the number of new cases reported for each day.\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn)\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "**Show the number of new cases for each day, for Italy, for March.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 31 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>day</th><th scope=col>new</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int64&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Italy</td><td>01</td><td>  NA</td></tr>\n",
       "\t<tr><td>Italy</td><td>02</td><td> 342</td></tr>\n",
       "\t<tr><td>Italy</td><td>03</td><td> 466</td></tr>\n",
       "\t<tr><td>Italy</td><td>04</td><td> 587</td></tr>\n",
       "\t<tr><td>Italy</td><td>05</td><td> 769</td></tr>\n",
       "\t<tr><td>Italy</td><td>06</td><td> 778</td></tr>\n",
       "\t<tr><td>Italy</td><td>07</td><td>1247</td></tr>\n",
       "\t<tr><td>Italy</td><td>08</td><td>1492</td></tr>\n",
       "\t<tr><td>Italy</td><td>09</td><td>1797</td></tr>\n",
       "\t<tr><td>Italy</td><td>10</td><td> 977</td></tr>\n",
       "\t<tr><td>⋮</td><td>⋮</td><td>⋮</td></tr>\n",
       "\t<tr><td>Italy</td><td>22</td><td>5560</td></tr>\n",
       "\t<tr><td>Italy</td><td>23</td><td>4789</td></tr>\n",
       "\t<tr><td>Italy</td><td>24</td><td>5249</td></tr>\n",
       "\t<tr><td>Italy</td><td>25</td><td>5210</td></tr>\n",
       "\t<tr><td>Italy</td><td>26</td><td>6203</td></tr>\n",
       "\t<tr><td>Italy</td><td>27</td><td>5909</td></tr>\n",
       "\t<tr><td>Italy</td><td>28</td><td>5974</td></tr>\n",
       "\t<tr><td>Italy</td><td>29</td><td>5217</td></tr>\n",
       "\t<tr><td>Italy</td><td>30</td><td>4050</td></tr>\n",
       "\t<tr><td>Italy</td><td>31</td><td>4053</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 31 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & day & new\\\\\n",
       " <chr> & <chr> & <int64>\\\\\n",
       "\\hline\n",
       "\t Italy & 01 &   NA\\\\\n",
       "\t Italy & 02 &  342\\\\\n",
       "\t Italy & 03 &  466\\\\\n",
       "\t Italy & 04 &  587\\\\\n",
       "\t Italy & 05 &  769\\\\\n",
       "\t Italy & 06 &  778\\\\\n",
       "\t Italy & 07 & 1247\\\\\n",
       "\t Italy & 08 & 1492\\\\\n",
       "\t Italy & 09 & 1797\\\\\n",
       "\t Italy & 10 &  977\\\\\n",
       "\t ⋮ & ⋮ & ⋮\\\\\n",
       "\t Italy & 22 & 5560\\\\\n",
       "\t Italy & 23 & 4789\\\\\n",
       "\t Italy & 24 & 5249\\\\\n",
       "\t Italy & 25 & 5210\\\\\n",
       "\t Italy & 26 & 6203\\\\\n",
       "\t Italy & 27 & 5909\\\\\n",
       "\t Italy & 28 & 5974\\\\\n",
       "\t Italy & 29 & 5217\\\\\n",
       "\t Italy & 30 & 4050\\\\\n",
       "\t Italy & 31 & 4053\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 31 × 3\n",
       "\n",
       "| name &lt;chr&gt; | day &lt;chr&gt; | new &lt;int64&gt; |\n",
       "|---|---|---|\n",
       "| Italy | 01 |   NA |\n",
       "| Italy | 02 |  342 |\n",
       "| Italy | 03 |  466 |\n",
       "| Italy | 04 |  587 |\n",
       "| Italy | 05 |  769 |\n",
       "| Italy | 06 |  778 |\n",
       "| Italy | 07 | 1247 |\n",
       "| Italy | 08 | 1492 |\n",
       "| Italy | 09 | 1797 |\n",
       "| Italy | 10 |  977 |\n",
       "| ⋮ | ⋮ | ⋮ |\n",
       "| Italy | 22 | 5560 |\n",
       "| Italy | 23 | 4789 |\n",
       "| Italy | 24 | 5249 |\n",
       "| Italy | 25 | 5210 |\n",
       "| Italy | 26 | 6203 |\n",
       "| Italy | 27 | 5909 |\n",
       "| Italy | 28 | 5974 |\n",
       "| Italy | 29 | 5217 |\n",
       "| Italy | 30 | 4050 |\n",
       "| Italy | 31 | 4053 |\n",
       "\n"
      ],
      "text/plain": [
       "   name  day new \n",
       "1  Italy 01    NA\n",
       "2  Italy 02   342\n",
       "3  Italy 03   466\n",
       "4  Italy 04   587\n",
       "5  Italy 05   769\n",
       "6  Italy 06   778\n",
       "7  Italy 07  1247\n",
       "8  Italy 08  1492\n",
       "9  Italy 09  1797\n",
       "10 Italy 10   977\n",
       "⋮  ⋮     ⋮   ⋮   \n",
       "22 Italy 22  5560\n",
       "23 Italy 23  4789\n",
       "24 Italy 24  5249\n",
       "25 Italy 25  5210\n",
       "26 Italy 26  6203\n",
       "27 Italy 27  5909\n",
       "28 Italy 28  5974\n",
       "29 Italy 29  5217\n",
       "30 Italy 30  4050\n",
       "31 Italy 31  4053"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "covid %>% \n",
    "    mutate(mo=format(whn, '%m'), \n",
    "           day=format(whn, '%d')) %>%\n",
    "    filter(name=='Italy' & mo=='03') %>%\n",
    "    group_by(name, mo) %>% \n",
    "    mutate(new=confirmed-lag(\n",
    "        confirmed, default=bit64::NA_integer64_, order_by=whn)) %>%\n",
    "    ungroup %>%\n",
    "    select(name, day, new)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Weekly changes\n",
    "\n",
    "The data gathered are necessarily estimates and are inaccurate. However by taking a longer time span we can mitigate some of the effects.\n",
    "\n",
    "You can filter the data to view only Monday's figures **WHERE WEEKDAY(whn) = 0**.\n",
    "\n",
    "```sql\n",
    "SELECT name, DATE_FORMAT(whn,'%Y-%m-%d'), confirmed\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND WEEKDAY(whn) = 0\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "**Show the number of new cases in Italy for each week - show Monday only.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 13 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>whn</th><th scope=col>new</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;int64&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Italy</td><td>2020-01-26</td><td>   NA</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-02-02</td><td>    2</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-02-09</td><td>    1</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-02-16</td><td>    0</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-02-23</td><td>  152</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-03-01</td><td> 1539</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-03-08</td><td> 5681</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-03-15</td><td>17372</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-03-22</td><td>34391</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-03-29</td><td>38551</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-04-05</td><td>31259</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-04-12</td><td>27415</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-04-19</td><td>22609</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 13 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & whn & new\\\\\n",
       " <chr> & <date> & <int64>\\\\\n",
       "\\hline\n",
       "\t Italy & 2020-01-26 &    NA\\\\\n",
       "\t Italy & 2020-02-02 &     2\\\\\n",
       "\t Italy & 2020-02-09 &     1\\\\\n",
       "\t Italy & 2020-02-16 &     0\\\\\n",
       "\t Italy & 2020-02-23 &   152\\\\\n",
       "\t Italy & 2020-03-01 &  1539\\\\\n",
       "\t Italy & 2020-03-08 &  5681\\\\\n",
       "\t Italy & 2020-03-15 & 17372\\\\\n",
       "\t Italy & 2020-03-22 & 34391\\\\\n",
       "\t Italy & 2020-03-29 & 38551\\\\\n",
       "\t Italy & 2020-04-05 & 31259\\\\\n",
       "\t Italy & 2020-04-12 & 27415\\\\\n",
       "\t Italy & 2020-04-19 & 22609\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 13 × 3\n",
       "\n",
       "| name &lt;chr&gt; | whn &lt;date&gt; | new &lt;int64&gt; |\n",
       "|---|---|---|\n",
       "| Italy | 2020-01-26 |    NA |\n",
       "| Italy | 2020-02-02 |     2 |\n",
       "| Italy | 2020-02-09 |     1 |\n",
       "| Italy | 2020-02-16 |     0 |\n",
       "| Italy | 2020-02-23 |   152 |\n",
       "| Italy | 2020-03-01 |  1539 |\n",
       "| Italy | 2020-03-08 |  5681 |\n",
       "| Italy | 2020-03-15 | 17372 |\n",
       "| Italy | 2020-03-22 | 34391 |\n",
       "| Italy | 2020-03-29 | 38551 |\n",
       "| Italy | 2020-04-05 | 31259 |\n",
       "| Italy | 2020-04-12 | 27415 |\n",
       "| Italy | 2020-04-19 | 22609 |\n",
       "\n"
      ],
      "text/plain": [
       "   name  whn        new  \n",
       "1  Italy 2020-01-26    NA\n",
       "2  Italy 2020-02-02     2\n",
       "3  Italy 2020-02-09     1\n",
       "4  Italy 2020-02-16     0\n",
       "5  Italy 2020-02-23   152\n",
       "6  Italy 2020-03-01  1539\n",
       "7  Italy 2020-03-08  5681\n",
       "8  Italy 2020-03-15 17372\n",
       "9  Italy 2020-03-22 34391\n",
       "10 Italy 2020-03-29 38551\n",
       "11 Italy 2020-04-05 31259\n",
       "12 Italy 2020-04-12 27415\n",
       "13 Italy 2020-04-19 22609"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "covid %>% \n",
    "    mutate(wday=format(whn, '%w')) %>%\n",
    "    filter(name=='Italy' & wday==0) %>%\n",
    "    mutate(\n",
    "        new=confirmed-lag(\n",
    "            confirmed, default=bit64::NA_integer64_, order_by=whn)) %>%\n",
    "    select(name, whn, new)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. LAG using a JOIN\n",
    "\n",
    "You can JOIN a table using DATE arithmetic. This will give different results if data is missing.\n",
    "\n",
    "**Show the number of new cases in Italy for each week - show Monday only.**\n",
    "\n",
    "In the sample query we JOIN this week tw with last week lw using the DATE_ADD function.\n",
    "\n",
    "```sql\n",
    "SELECT tw.name, DATE_FORMAT(tw.whn,'%Y-%m-%d'), \n",
    " tw.confirmed, lw.confirmed\n",
    " FROM covid tw LEFT JOIN covid lw ON \n",
    "  DATE_ADD(lw.whn, INTERVAL 1 WEEK) = tw.whn\n",
    "   AND tw.name=lw.name\n",
    "WHERE tw.name = 'Italy'\n",
    "ORDER BY tw.whn\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 13 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>whn</th><th scope=col>new</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;int64&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Italy</td><td>2020-01-26</td><td>   NA</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-02-02</td><td>    2</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-02-09</td><td>    1</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-02-16</td><td>    0</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-02-23</td><td>  152</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-03-01</td><td> 1539</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-03-08</td><td> 5681</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-03-15</td><td>17372</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-03-22</td><td>34391</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-03-29</td><td>38551</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-04-05</td><td>31259</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-04-12</td><td>27415</td></tr>\n",
       "\t<tr><td>Italy</td><td>2020-04-19</td><td>22609</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 13 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & whn & new\\\\\n",
       " <chr> & <date> & <int64>\\\\\n",
       "\\hline\n",
       "\t Italy & 2020-01-26 &    NA\\\\\n",
       "\t Italy & 2020-02-02 &     2\\\\\n",
       "\t Italy & 2020-02-09 &     1\\\\\n",
       "\t Italy & 2020-02-16 &     0\\\\\n",
       "\t Italy & 2020-02-23 &   152\\\\\n",
       "\t Italy & 2020-03-01 &  1539\\\\\n",
       "\t Italy & 2020-03-08 &  5681\\\\\n",
       "\t Italy & 2020-03-15 & 17372\\\\\n",
       "\t Italy & 2020-03-22 & 34391\\\\\n",
       "\t Italy & 2020-03-29 & 38551\\\\\n",
       "\t Italy & 2020-04-05 & 31259\\\\\n",
       "\t Italy & 2020-04-12 & 27415\\\\\n",
       "\t Italy & 2020-04-19 & 22609\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 13 × 3\n",
       "\n",
       "| name &lt;chr&gt; | whn &lt;date&gt; | new &lt;int64&gt; |\n",
       "|---|---|---|\n",
       "| Italy | 2020-01-26 |    NA |\n",
       "| Italy | 2020-02-02 |     2 |\n",
       "| Italy | 2020-02-09 |     1 |\n",
       "| Italy | 2020-02-16 |     0 |\n",
       "| Italy | 2020-02-23 |   152 |\n",
       "| Italy | 2020-03-01 |  1539 |\n",
       "| Italy | 2020-03-08 |  5681 |\n",
       "| Italy | 2020-03-15 | 17372 |\n",
       "| Italy | 2020-03-22 | 34391 |\n",
       "| Italy | 2020-03-29 | 38551 |\n",
       "| Italy | 2020-04-05 | 31259 |\n",
       "| Italy | 2020-04-12 | 27415 |\n",
       "| Italy | 2020-04-19 | 22609 |\n",
       "\n"
      ],
      "text/plain": [
       "   name  whn        new  \n",
       "1  Italy 2020-01-26    NA\n",
       "2  Italy 2020-02-02     2\n",
       "3  Italy 2020-02-09     1\n",
       "4  Italy 2020-02-16     0\n",
       "5  Italy 2020-02-23   152\n",
       "6  Italy 2020-03-01  1539\n",
       "7  Italy 2020-03-08  5681\n",
       "8  Italy 2020-03-15 17372\n",
       "9  Italy 2020-03-22 34391\n",
       "10 Italy 2020-03-29 38551\n",
       "11 Italy 2020-04-05 31259\n",
       "12 Italy 2020-04-12 27415\n",
       "13 Italy 2020-04-19 22609"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "a <- covid %>% \n",
    "    mutate(wday=format(whn, '%w')) %>% \n",
    "    filter(name=='Italy' & wday=='0')\n",
    "a %>% left_join(\n",
    "    a %>% mutate(\n",
    "        whn=whn+7, \n",
    "        confirmed=bit64::as.character.integer64(confirmed)) %>% \n",
    "        select(name, whn, confirmed), \n",
    "    by=c(name='name', whn='whn')) %>%\n",
    "    mutate(new=confirmed.x-confirmed.y) %>%\n",
    "    select(name, whn, new)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. RANK()\n",
    "\n",
    "The query shown shows the number of confirmed cases together with the world ranking for cases.\n",
    "\n",
    "United States has the highest number, Spain is number 2...\n",
    "\n",
    "Notice that while Spain has the second highest confirmed cases, Italy has the second highest number of deaths due to the virus.\n",
    "\n",
    "```sql\n",
    "SELECT \n",
    "   name,\n",
    "   confirmed,\n",
    "   RANK() OVER (ORDER BY confirmed DESC) rc,\n",
    "   deaths\n",
    "  FROM covid\n",
    "WHERE whn = '2020-04-20'\n",
    "ORDER BY confirmed DESC\n",
    "```\n",
    "\n",
    "**Include the ranking for the number of deaths in the table. Only include countries with a population of at least 10 million.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 90 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>confirmed</th><th scope=col>rc</th><th scope=col>deaths</th><th scope=col>rc2</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int64&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int64&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>United States </td><td>784326</td><td> 1</td><td>42094</td><td> 1</td></tr>\n",
       "\t<tr><td>Spain         </td><td>200210</td><td> 2</td><td>20852</td><td> 3</td></tr>\n",
       "\t<tr><td>Italy         </td><td>181228</td><td> 3</td><td>24114</td><td> 2</td></tr>\n",
       "\t<tr><td>France        </td><td>156480</td><td> 4</td><td>20292</td><td> 4</td></tr>\n",
       "\t<tr><td>Germany       </td><td>147065</td><td> 5</td><td> 4862</td><td> 8</td></tr>\n",
       "\t<tr><td>United Kingdom</td><td>125856</td><td> 6</td><td>16550</td><td> 5</td></tr>\n",
       "\t<tr><td>Turkey        </td><td> 90980</td><td> 7</td><td> 2140</td><td>12</td></tr>\n",
       "\t<tr><td>China         </td><td> 83817</td><td> 8</td><td> 4636</td><td> 9</td></tr>\n",
       "\t<tr><td>Iran          </td><td> 83505</td><td> 9</td><td> 5209</td><td> 7</td></tr>\n",
       "\t<tr><td>Russia        </td><td> 47121</td><td>10</td><td>  405</td><td>23</td></tr>\n",
       "\t<tr><td>⋮</td><td>⋮</td><td>⋮</td><td>⋮</td><td>⋮</td></tr>\n",
       "\t<tr><td>Mozambique </td><td>39</td><td>81</td><td>0</td><td>84</td></tr>\n",
       "\t<tr><td>Syria      </td><td>39</td><td>82</td><td>3</td><td>74</td></tr>\n",
       "\t<tr><td>Chad       </td><td>33</td><td>83</td><td>0</td><td>82</td></tr>\n",
       "\t<tr><td>Nepal      </td><td>31</td><td>84</td><td>0</td><td>85</td></tr>\n",
       "\t<tr><td>Zimbabwe   </td><td>25</td><td>85</td><td>3</td><td>76</td></tr>\n",
       "\t<tr><td>Angola     </td><td>24</td><td>86</td><td>2</td><td>77</td></tr>\n",
       "\t<tr><td>Malawi     </td><td>17</td><td>87</td><td>2</td><td>78</td></tr>\n",
       "\t<tr><td>Burundi    </td><td> 5</td><td>88</td><td>1</td><td>80</td></tr>\n",
       "\t<tr><td>South Sudan</td><td> 4</td><td>89</td><td>0</td><td>87</td></tr>\n",
       "\t<tr><td>Yemen      </td><td> 1</td><td>90</td><td>0</td><td>90</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 90 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " name & confirmed & rc & deaths & rc2\\\\\n",
       " <chr> & <int64> & <int> & <int64> & <int>\\\\\n",
       "\\hline\n",
       "\t United States  & 784326 &  1 & 42094 &  1\\\\\n",
       "\t Spain          & 200210 &  2 & 20852 &  3\\\\\n",
       "\t Italy          & 181228 &  3 & 24114 &  2\\\\\n",
       "\t France         & 156480 &  4 & 20292 &  4\\\\\n",
       "\t Germany        & 147065 &  5 &  4862 &  8\\\\\n",
       "\t United Kingdom & 125856 &  6 & 16550 &  5\\\\\n",
       "\t Turkey         &  90980 &  7 &  2140 & 12\\\\\n",
       "\t China          &  83817 &  8 &  4636 &  9\\\\\n",
       "\t Iran           &  83505 &  9 &  5209 &  7\\\\\n",
       "\t Russia         &  47121 & 10 &   405 & 23\\\\\n",
       "\t ⋮ & ⋮ & ⋮ & ⋮ & ⋮\\\\\n",
       "\t Mozambique  & 39 & 81 & 0 & 84\\\\\n",
       "\t Syria       & 39 & 82 & 3 & 74\\\\\n",
       "\t Chad        & 33 & 83 & 0 & 82\\\\\n",
       "\t Nepal       & 31 & 84 & 0 & 85\\\\\n",
       "\t Zimbabwe    & 25 & 85 & 3 & 76\\\\\n",
       "\t Angola      & 24 & 86 & 2 & 77\\\\\n",
       "\t Malawi      & 17 & 87 & 2 & 78\\\\\n",
       "\t Burundi     &  5 & 88 & 1 & 80\\\\\n",
       "\t South Sudan &  4 & 89 & 0 & 87\\\\\n",
       "\t Yemen       &  1 & 90 & 0 & 90\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 90 × 5\n",
       "\n",
       "| name &lt;chr&gt; | confirmed &lt;int64&gt; | rc &lt;int&gt; | deaths &lt;int64&gt; | rc2 &lt;int&gt; |\n",
       "|---|---|---|---|---|\n",
       "| United States  | 784326 |  1 | 42094 |  1 |\n",
       "| Spain          | 200210 |  2 | 20852 |  3 |\n",
       "| Italy          | 181228 |  3 | 24114 |  2 |\n",
       "| France         | 156480 |  4 | 20292 |  4 |\n",
       "| Germany        | 147065 |  5 |  4862 |  8 |\n",
       "| United Kingdom | 125856 |  6 | 16550 |  5 |\n",
       "| Turkey         |  90980 |  7 |  2140 | 12 |\n",
       "| China          |  83817 |  8 |  4636 |  9 |\n",
       "| Iran           |  83505 |  9 |  5209 |  7 |\n",
       "| Russia         |  47121 | 10 |   405 | 23 |\n",
       "| ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |\n",
       "| Mozambique  | 39 | 81 | 0 | 84 |\n",
       "| Syria       | 39 | 82 | 3 | 74 |\n",
       "| Chad        | 33 | 83 | 0 | 82 |\n",
       "| Nepal       | 31 | 84 | 0 | 85 |\n",
       "| Zimbabwe    | 25 | 85 | 3 | 76 |\n",
       "| Angola      | 24 | 86 | 2 | 77 |\n",
       "| Malawi      | 17 | 87 | 2 | 78 |\n",
       "| Burundi     |  5 | 88 | 1 | 80 |\n",
       "| South Sudan |  4 | 89 | 0 | 87 |\n",
       "| Yemen       |  1 | 90 | 0 | 90 |\n",
       "\n"
      ],
      "text/plain": [
       "   name           confirmed rc deaths rc2\n",
       "1  United States  784326     1 42094   1 \n",
       "2  Spain          200210     2 20852   3 \n",
       "3  Italy          181228     3 24114   2 \n",
       "4  France         156480     4 20292   4 \n",
       "5  Germany        147065     5  4862   8 \n",
       "6  United Kingdom 125856     6 16550   5 \n",
       "7  Turkey          90980     7  2140  12 \n",
       "8  China           83817     8  4636   9 \n",
       "9  Iran            83505     9  5209   7 \n",
       "10 Russia          47121    10   405  23 \n",
       "⋮  ⋮              ⋮         ⋮  ⋮      ⋮  \n",
       "81 Mozambique     39        81 0      84 \n",
       "82 Syria          39        82 3      74 \n",
       "83 Chad           33        83 0      82 \n",
       "84 Nepal          31        84 0      85 \n",
       "85 Zimbabwe       25        85 3      76 \n",
       "86 Angola         24        86 2      77 \n",
       "87 Malawi         17        87 2      78 \n",
       "88 Burundi         5        88 1      80 \n",
       "89 South Sudan     4        89 0      87 \n",
       "90 Yemen           1        90 0      90 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "covid %>%\n",
    "    inner_join(world %>% \n",
    "               select(name, population), by=c(name='name')) %>%\n",
    "    filter(whn==as.Date('2020-04-20') & population>=1e7) %>%\n",
    "    mutate(rc=row_number(desc(confirmed)), \n",
    "           rc2=row_number(desc(deaths))) %>%\n",
    "    select(name, confirmed, rc, deaths, rc2) %>%\n",
    "    arrange(desc(confirmed))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Infection rate\n",
    "\n",
    "The query shown includes a JOIN t the world table so we can access the total population of each country and calculate infection rates (in cases per 100,000).\n",
    "\n",
    "```sql\n",
    "SELECT \n",
    "   world.name,\n",
    "   ROUND(100000*confirmed/population,0)\n",
    "  FROM covid JOIN world ON covid.name=world.name\n",
    "WHERE whn = '2020-04-20' AND population > 10000000\n",
    "ORDER BY population DESC\n",
    "```\n",
    "\n",
    "**Show the infect rate ranking for each country. Only include countries with a population of at least 10 million.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 90 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>inf_rate</th><th scope=col>rank</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>China        </td><td>  6</td><td>51</td></tr>\n",
       "\t<tr><td>India        </td><td>  1</td><td>24</td></tr>\n",
       "\t<tr><td>United States</td><td>238</td><td>87</td></tr>\n",
       "\t<tr><td>Indonesia    </td><td>  3</td><td>39</td></tr>\n",
       "\t<tr><td>Pakistan     </td><td>  4</td><td>45</td></tr>\n",
       "\t<tr><td>Brazil       </td><td> 19</td><td>65</td></tr>\n",
       "\t<tr><td>Nigeria      </td><td>  0</td><td>13</td></tr>\n",
       "\t<tr><td>Bangladesh   </td><td>  2</td><td>31</td></tr>\n",
       "\t<tr><td>Russia       </td><td> 32</td><td>71</td></tr>\n",
       "\t<tr><td>Mexico       </td><td>  7</td><td>55</td></tr>\n",
       "\t<tr><td>⋮</td><td>⋮</td><td>⋮</td></tr>\n",
       "\t<tr><td>Bolivia           </td><td>  5</td><td>47</td></tr>\n",
       "\t<tr><td>Cuba              </td><td> 10</td><td>60</td></tr>\n",
       "\t<tr><td>Burundi           </td><td>  0</td><td> 3</td></tr>\n",
       "\t<tr><td>Greece            </td><td> 21</td><td>66</td></tr>\n",
       "\t<tr><td>Czech Republic    </td><td> 65</td><td>77</td></tr>\n",
       "\t<tr><td>Jordan            </td><td>  4</td><td>44</td></tr>\n",
       "\t<tr><td>Dominican Republic</td><td> 48</td><td>73</td></tr>\n",
       "\t<tr><td>Sweden            </td><td>143</td><td>81</td></tr>\n",
       "\t<tr><td>Portugal          </td><td>203</td><td>85</td></tr>\n",
       "\t<tr><td>Azerbaijan        </td><td> 14</td><td>62</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 90 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & inf\\_rate & rank\\\\\n",
       " <chr> & <dbl> & <int>\\\\\n",
       "\\hline\n",
       "\t China         &   6 & 51\\\\\n",
       "\t India         &   1 & 24\\\\\n",
       "\t United States & 238 & 87\\\\\n",
       "\t Indonesia     &   3 & 39\\\\\n",
       "\t Pakistan      &   4 & 45\\\\\n",
       "\t Brazil        &  19 & 65\\\\\n",
       "\t Nigeria       &   0 & 13\\\\\n",
       "\t Bangladesh    &   2 & 31\\\\\n",
       "\t Russia        &  32 & 71\\\\\n",
       "\t Mexico        &   7 & 55\\\\\n",
       "\t ⋮ & ⋮ & ⋮\\\\\n",
       "\t Bolivia            &   5 & 47\\\\\n",
       "\t Cuba               &  10 & 60\\\\\n",
       "\t Burundi            &   0 &  3\\\\\n",
       "\t Greece             &  21 & 66\\\\\n",
       "\t Czech Republic     &  65 & 77\\\\\n",
       "\t Jordan             &   4 & 44\\\\\n",
       "\t Dominican Republic &  48 & 73\\\\\n",
       "\t Sweden             & 143 & 81\\\\\n",
       "\t Portugal           & 203 & 85\\\\\n",
       "\t Azerbaijan         &  14 & 62\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 90 × 3\n",
       "\n",
       "| name &lt;chr&gt; | inf_rate &lt;dbl&gt; | rank &lt;int&gt; |\n",
       "|---|---|---|\n",
       "| China         |   6 | 51 |\n",
       "| India         |   1 | 24 |\n",
       "| United States | 238 | 87 |\n",
       "| Indonesia     |   3 | 39 |\n",
       "| Pakistan      |   4 | 45 |\n",
       "| Brazil        |  19 | 65 |\n",
       "| Nigeria       |   0 | 13 |\n",
       "| Bangladesh    |   2 | 31 |\n",
       "| Russia        |  32 | 71 |\n",
       "| Mexico        |   7 | 55 |\n",
       "| ⋮ | ⋮ | ⋮ |\n",
       "| Bolivia            |   5 | 47 |\n",
       "| Cuba               |  10 | 60 |\n",
       "| Burundi            |   0 |  3 |\n",
       "| Greece             |  21 | 66 |\n",
       "| Czech Republic     |  65 | 77 |\n",
       "| Jordan             |   4 | 44 |\n",
       "| Dominican Republic |  48 | 73 |\n",
       "| Sweden             | 143 | 81 |\n",
       "| Portugal           | 203 | 85 |\n",
       "| Azerbaijan         |  14 | 62 |\n",
       "\n"
      ],
      "text/plain": [
       "   name               inf_rate rank\n",
       "1  China                6      51  \n",
       "2  India                1      24  \n",
       "3  United States      238      87  \n",
       "4  Indonesia            3      39  \n",
       "5  Pakistan             4      45  \n",
       "6  Brazil              19      65  \n",
       "7  Nigeria              0      13  \n",
       "8  Bangladesh           2      31  \n",
       "9  Russia              32      71  \n",
       "10 Mexico               7      55  \n",
       "⋮  ⋮                  ⋮        ⋮   \n",
       "81 Bolivia              5      47  \n",
       "82 Cuba                10      60  \n",
       "83 Burundi              0       3  \n",
       "84 Greece              21      66  \n",
       "85 Czech Republic      65      77  \n",
       "86 Jordan               4      44  \n",
       "87 Dominican Republic  48      73  \n",
       "88 Sweden             143      81  \n",
       "89 Portugal           203      85  \n",
       "90 Azerbaijan          14      62  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "covid %>%\n",
    "    inner_join(world %>% \n",
    "               select(name, population), by=c(name='name')) %>%\n",
    "    filter(whn==as.Date('2020-04-20') & population>=1e7) %>%\n",
    "    mutate(inf_rate=round(1e5 * confirmed/population, 0)) %>%\n",
    "    mutate(rank=row_number(inf_rate)) %>%\n",
    "    arrange(desc(population)) %>%\n",
    "    select(name, inf_rate, rank)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Turning the corner\n",
    "\n",
    "For each country that has had at last 1000 new cases in a single day, show the date of the peak number of new cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A grouped_df: 26 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>whn</th><th scope=col>new</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;int64&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>China      </td><td>2020-02-13</td><td>15136</td></tr>\n",
       "\t<tr><td>Italy      </td><td>2020-03-21</td><td> 6557</td></tr>\n",
       "\t<tr><td>Switzerland</td><td>2020-03-23</td><td> 1321</td></tr>\n",
       "\t<tr><td>Israel     </td><td>2020-03-25</td><td> 1131</td></tr>\n",
       "\t<tr><td>Spain      </td><td>2020-03-25</td><td> 9630</td></tr>\n",
       "\t<tr><td>Austria    </td><td>2020-03-26</td><td> 1321</td></tr>\n",
       "\t<tr><td>Germany    </td><td>2020-03-27</td><td> 6933</td></tr>\n",
       "\t<tr><td>Iran       </td><td>2020-03-30</td><td> 3186</td></tr>\n",
       "\t<tr><td>Canada     </td><td>2020-04-05</td><td> 2778</td></tr>\n",
       "\t<tr><td>Ecuador    </td><td>2020-04-10</td><td> 2196</td></tr>\n",
       "\t<tr><td>⋮</td><td>⋮</td><td>⋮</td></tr>\n",
       "\t<tr><td>France      </td><td>2020-04-12</td><td>26849</td></tr>\n",
       "\t<tr><td>Peru        </td><td>2020-04-13</td><td> 2265</td></tr>\n",
       "\t<tr><td>Belgium     </td><td>2020-04-15</td><td> 2454</td></tr>\n",
       "\t<tr><td>Brazil      </td><td>2020-04-17</td><td> 3257</td></tr>\n",
       "\t<tr><td>Japan       </td><td>2020-04-17</td><td> 1161</td></tr>\n",
       "\t<tr><td>Saudi Arabia</td><td>2020-04-18</td><td> 1132</td></tr>\n",
       "\t<tr><td>India       </td><td>2020-04-19</td><td> 1893</td></tr>\n",
       "\t<tr><td>Russia      </td><td>2020-04-19</td><td> 6060</td></tr>\n",
       "\t<tr><td>Belarus     </td><td>2020-04-20</td><td> 1485</td></tr>\n",
       "\t<tr><td>Singapore   </td><td>2020-04-20</td><td> 1426</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 26 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & whn & new\\\\\n",
       " <chr> & <date> & <int64>\\\\\n",
       "\\hline\n",
       "\t China       & 2020-02-13 & 15136\\\\\n",
       "\t Italy       & 2020-03-21 &  6557\\\\\n",
       "\t Switzerland & 2020-03-23 &  1321\\\\\n",
       "\t Israel      & 2020-03-25 &  1131\\\\\n",
       "\t Spain       & 2020-03-25 &  9630\\\\\n",
       "\t Austria     & 2020-03-26 &  1321\\\\\n",
       "\t Germany     & 2020-03-27 &  6933\\\\\n",
       "\t Iran        & 2020-03-30 &  3186\\\\\n",
       "\t Canada      & 2020-04-05 &  2778\\\\\n",
       "\t Ecuador     & 2020-04-10 &  2196\\\\\n",
       "\t ⋮ & ⋮ & ⋮\\\\\n",
       "\t France       & 2020-04-12 & 26849\\\\\n",
       "\t Peru         & 2020-04-13 &  2265\\\\\n",
       "\t Belgium      & 2020-04-15 &  2454\\\\\n",
       "\t Brazil       & 2020-04-17 &  3257\\\\\n",
       "\t Japan        & 2020-04-17 &  1161\\\\\n",
       "\t Saudi Arabia & 2020-04-18 &  1132\\\\\n",
       "\t India        & 2020-04-19 &  1893\\\\\n",
       "\t Russia       & 2020-04-19 &  6060\\\\\n",
       "\t Belarus      & 2020-04-20 &  1485\\\\\n",
       "\t Singapore    & 2020-04-20 &  1426\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 26 × 3\n",
       "\n",
       "| name &lt;chr&gt; | whn &lt;date&gt; | new &lt;int64&gt; |\n",
       "|---|---|---|\n",
       "| China       | 2020-02-13 | 15136 |\n",
       "| Italy       | 2020-03-21 |  6557 |\n",
       "| Switzerland | 2020-03-23 |  1321 |\n",
       "| Israel      | 2020-03-25 |  1131 |\n",
       "| Spain       | 2020-03-25 |  9630 |\n",
       "| Austria     | 2020-03-26 |  1321 |\n",
       "| Germany     | 2020-03-27 |  6933 |\n",
       "| Iran        | 2020-03-30 |  3186 |\n",
       "| Canada      | 2020-04-05 |  2778 |\n",
       "| Ecuador     | 2020-04-10 |  2196 |\n",
       "| ⋮ | ⋮ | ⋮ |\n",
       "| France       | 2020-04-12 | 26849 |\n",
       "| Peru         | 2020-04-13 |  2265 |\n",
       "| Belgium      | 2020-04-15 |  2454 |\n",
       "| Brazil       | 2020-04-17 |  3257 |\n",
       "| Japan        | 2020-04-17 |  1161 |\n",
       "| Saudi Arabia | 2020-04-18 |  1132 |\n",
       "| India        | 2020-04-19 |  1893 |\n",
       "| Russia       | 2020-04-19 |  6060 |\n",
       "| Belarus      | 2020-04-20 |  1485 |\n",
       "| Singapore    | 2020-04-20 |  1426 |\n",
       "\n"
      ],
      "text/plain": [
       "   name         whn        new  \n",
       "1  China        2020-02-13 15136\n",
       "2  Italy        2020-03-21  6557\n",
       "3  Switzerland  2020-03-23  1321\n",
       "4  Israel       2020-03-25  1131\n",
       "5  Spain        2020-03-25  9630\n",
       "6  Austria      2020-03-26  1321\n",
       "7  Germany      2020-03-27  6933\n",
       "8  Iran         2020-03-30  3186\n",
       "9  Canada       2020-04-05  2778\n",
       "10 Ecuador      2020-04-10  2196\n",
       "⋮  ⋮            ⋮          ⋮    \n",
       "17 France       2020-04-12 26849\n",
       "18 Peru         2020-04-13  2265\n",
       "19 Belgium      2020-04-15  2454\n",
       "20 Brazil       2020-04-17  3257\n",
       "21 Japan        2020-04-17  1161\n",
       "22 Saudi Arabia 2020-04-18  1132\n",
       "23 India        2020-04-19  1893\n",
       "24 Russia       2020-04-19  6060\n",
       "25 Belarus      2020-04-20  1485\n",
       "26 Singapore    2020-04-20  1426"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "covid %>%\n",
    "    group_by(name) %>%\n",
    "    mutate(new=confirmed-lag(\n",
    "        confirmed, default=bit64::NA_integer64_, order_by=whn)) %>%\n",
    "    mutate(rank=row_number(desc(new))) %>%\n",
    "    filter(rank==1 & new>=1000) %>%\n",
    "    select(name, whn, new) %>%\n",
    "    arrange(whn, name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.0.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
